package websimulator;

import java.util.*;
import java.lang.reflect.Array;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.mysql.jdbc.Statement;

public class Connector
{
    /**
     * Necessary variables
     */
    private String url;                 //url to DB
    private String username;            //DB username
    private String password;            //DB password
    private Connection connecton;       //variable for connection
    private String query;               //DB query
    private PreparedStatement statment; //create query
    private ResultSet result;           //variable to execute query
    private boolean userExist;          //check at user is in the DB
    /**
     * Constructor
     */
    public Connector() throws Exception
    {
        //obtain the necessary information from the configuration file
        Config config = new Config();
        url = config.getUrl();
        username = config.getUserName();
        password = config.getPassword();
        //Accessing driver
        Class.forName("com.mysql.jdbc.Driver");
        //establish a database connection
        connecton = DriverManager.getConnection(url, username, password);
    }
    
    /**
     * 
     * @param _username - username
     * @param _password - password
     * @return true if username and password correct, and false if they don't correct
     * @throws Exception 
     */
    public boolean LoginStatement(String _username, String _password) throws Exception
    {   
        //Set that we dont user
        userExist = false;
        //query which returns list with users from User table
        query = "select UserName from User";
        //Send our query to statment
        statment = connecton.prepareStatement(query);
        //Execute query
        result = statment.executeQuery();
        
        //Check all users from the DB
        while(result.next())
        {
            if(result.getString("UserName").equals(_username))
            {
                userExist = true;
                //System.out.println(result.getString("UserName"));
                break;
            }
        }
        
        //If we found user in the DB we should check user's password
        if (userExist = true)
        {
            //query which select password for specified user
            query = "select Password from User where UserName = " + "'" +_username + "'";
            //System.out.println(query);
            statment = connecton.prepareStatement(query);
            result = statment.executeQuery();
            result.next();
            
            //Compair the specified password with the saved one
            if(result.getString("Password").equals(_password))
            {  
                /*System.out.println("Password correct:");
                System.out.println("Username - " + _username);
                System.out.println("Password - " + result.getString("Password"));*/   
                userExist = false;
                return true;
            }
            else
            {
                System.out.println("Password incorrect");
                userExist = false;
                return false;
            }
        }
        //User with specified name doesn't exist in the DB
        else
        {
            userExist = false;
            return false;
        }
        
        
        
        }
    
    
    //Method to save a simulation to the database
    /*public void SaveSimulation(String values, int houseID, int userID, String simulationStart, String simulationStop) throws SQLException
    {
    	String _values = values;
    	int _houseID = houseID;
    	int _userID = userID;
    	String _simulationStart = simulationStart;
    	String _simulationStop = simulationStop;
    	   	
    	query = "INSERT INTO Simulations (values, houseID, userID, simulationStart, simulationStop) VALUES (?, ?, ?, ?, ?)";
        
    	statment = connecton.prepareStatement(query);
    	
    	statment.setString(1,_values);
    	statment.setInt(2,_houseID);
    	statment.setInt(3,_userID);
    	statment.setString(4,_simulationStart);
    	statment.setString(5,_simulationStop);
    	
    	
    	//statment = connecton.prepareStatement(query);
    	statment.executeUpdate();
    	
    	/*result = statment.executeQuery();
    	result.next();
    }*/
    
    
    //Get direktiv from the DB and load it into a array
    public ArrayList GetArraydirektiv(int ID) throws SQLException
    {
    	
    	ArrayList direktiv = new ArrayList();
    	
    	query = "select * from Direktiver where ID =  " + "'" +ID + "'";
    	
		statment = connecton.prepareStatement(query);
		result = statment.executeQuery();
		
		while (result.next()) 
		{
			direktiv.add(result.getString(2));
			direktiv.add(result.getInt(3));
			direktiv.add(result.getInt(4));
			direktiv.add(result.getInt(5));
			direktiv.add(result.getInt(6));
		}
		
		return direktiv;
        
   }
   
   //Get direktiv from the DB and load it into a string
   public String GetStringdirektiv(int ID) throws SQLException
   {
	   
	   //String test;
	   
	   ArrayList<String> direktiv = new ArrayList<String>();
	   
	   query = "SELECT * FROM Direktiver WHERE ID = " + "'" +ID + "'";
	
		statment = connecton.prepareStatement(query);
		result = statment.executeQuery();

		while (result.next()) 
		{
			//direktiv.add(result.getString(1));
			direktiv.add(result.getString(2));
			direktiv.add(result.getString(3));
			direktiv.add(result.getString(4));
			direktiv.add(result.getString(5));
			direktiv.add(result.getString(6));
		}
		
		String direktivet = "";
		
		for (String s : direktiv)
		{
			direktivet += s + "\t";
		}
		
		return direktivet;
	}
   
   //Leg til nytt direktiv
   public void AddDirektiv(String Navn, double U_Value_Roof, double U_Value_Floor, double U_Value_Walls, double U_Value_Windows) throws SQLException
   {
	   String _Navn = Navn;
	   double _U_Value_Roof = U_Value_Roof;
	   double _U_Value_Floor = U_Value_Floor;
	   double _U_Value_Walls = U_Value_Walls;
	   double _U_Value_Windows = U_Value_Windows;
	   
	   query = "INSERT INTO Direktiver (Navn, u_value_roof, u_value_floor, u_value_walls, u_value_windows) VALUES(?,?,?,?,?)";
	   
	   statment = connecton.prepareStatement(query);
	   
	   statment.setString(1,_Navn);
	   statment.setDouble(2,_U_Value_Roof);
	   statment.setDouble(3,_U_Value_Floor);
	   statment.setDouble(4,_U_Value_Walls);
	   statment.setDouble(5,_U_Value_Windows);
	   
	   statment.executeUpdate();
   }
   
   //Remove direktiv
   public void RemoveDirektiv(int ID) throws SQLException
   {
	   int _ID = ID;
	   
	   query = "DELETE FROM Direktiver WHERE id = ?";
	   statment = connecton.prepareStatement(query);
	   statment.setInt(1,ID);
	  
	   statment.executeUpdate();
   }
   
   //returns the ID of a direktiv
   public String GetDirektivID(String Name) throws SQLException
   {
	   ArrayList<String> userID = new ArrayList<String>();
	   
	   query = "SELECT ID FROM Direktiver WHERE Navn = " + "'" +Name + "'";
	   
	   statment = connecton.prepareStatement(query);
	   result = statment.executeQuery();
	   
	   while (result.next())
	   {
		   userID.add(result.getString(1));
	   }
	   
		String ID = "";
		
		for (String s : userID)
		{
			ID += s + "\t";
		}
		
		return ID;
   }
   
   //add new user
   public void AddUser(String Name, String Password) throws SQLException
   {
	   String _Name = Name;
	   String _Password = Password;
	   
	   query = "INSERT INTO User (UserName, Password) VALUES(?,?)";
	   
	   statment = connecton.prepareStatement(query);
	   
	   statment.setString(1,_Name);
	   statment.setString(2,_Password);
	   
	   statment.executeUpdate();
   }
   
   //remove a user
   public void RemoveUser(int ID) throws SQLException
   {
	   query = "DELETE FROM User WHERE id = ?";
	   statment = connecton.prepareStatement(query);
	   statment.setInt(1,ID);
	  
	   statment.executeUpdate();
   }
   
   //Returns the users ID
   public String GetUserID(String name) throws SQLException
   {
	   
	   ArrayList<String> userID = new ArrayList<String>();
	   
	   query = "SELECT id FROM User WHERE UserName = " + "'" +name + "'";
	   
	   statment = connecton.prepareStatement(query);
	   result = statment.executeQuery();
	   
	   while (result.next())
	   {
		   userID.add(result.getString(1));
	   }
	   
		String ID = "";
		
		for (String s : userID)
		{
			ID += s;
		}
		
		return ID;
	   
   }
   
   //Returns the users Password
   public String GetUserspass(String name) throws SQLException
   {
	   ArrayList<String> userPass = new ArrayList<String>();
	   
	   query = "SELECT Password FROM User WHERE UserName = " + "'" +name + "'";
	   
	   statment = connecton.prepareStatement(query);
	   result = statment.executeQuery();
	   
	   while (result.next())
	   {
		   userPass.add(result.getString(1));
	   }
	   
		String pass = "";
		
		for (String s : userPass)
		{
			pass += s + "\t";
		}
		
		return pass;
   }
   
   //Change the users name
   public void ChangeUserName(String OldName, String NewName) throws SQLException
   {
	   String _OldName = OldName;
	   String _NewName = NewName;
	   
	   query = "UPDATE User SET UserName = ? WHERE UserName = ?";
	   
	   statment = connecton.prepareStatement(query);
	   
	   statment.setString(1,_NewName);
	   statment.setString(2,_OldName);
	   
	   statment.executeUpdate();
   }
   
   //Change the users password
   public void ChangeUserPassword(String Name, String NewPassword) throws SQLException
   {
	   String _Name = Name;
	   String _NewPassword = NewPassword;
	   
	   query = "UPDATE User SET Password = ? WHERE UserName = ?";
	   
	   statment = connecton.prepareStatement(query);
	   
	   statment.setString(1,_NewPassword);
	   statment.setString(2,_Name);
	   
	   statment.executeUpdate();
   }
   
   //Legg til nytt hus.
   public void AddHouse(int People, int Area, int Directive, double WallsHeight, int UserID) throws SQLException
   {
	   int _People = People;
	   int _Area = Area;
	   int _Directive = Directive;
	   double _WallsHeight = WallsHeight;
	   int _UserID = UserID;
	   
	   query = "INSERT INTO House (People, Area, Directive, WallsHeight, UserID) VALUES(?,?,?,?,?)";
	   
	   statment = connecton.prepareStatement(query);
	   
	   statment.setInt(1,_People);
	   statment.setInt(2,_Area);
	   statment.setInt(3,_Directive);
	   statment.setDouble(4,_WallsHeight);
	   statment.setInt(5,_UserID);
	   
	   statment.executeUpdate();
   }
   
   //returnerer verdiene/parametrene til et hus.
   public String GetHouseValues(int ID) throws SQLException
   {
	   
	   ArrayList<String> house = new ArrayList<String>();
	   
	   query = "SELECT * FROM House WHERE ID = " + "'" +ID + "'";
	
		statment = connecton.prepareStatement(query);
		result = statment.executeQuery();

		while (result.next()) 
		{
			//house.add(result.getString(1));
			house.add(result.getString(2));
			house.add(result.getString(3));
			house.add(result.getString(4));
			house.add(result.getString(5));
			house.add(result.getString(6));
		}
		
		String aHouse = "";
		
		for (String s : house)
		{
			aHouse += s + "\t";
		}
		
		return aHouse;
	}
   
   //returnerer verdiene/parametrene til alle hus i databasen.
   public String GetAllHouseValues() throws SQLException
   {
	   
	   ArrayList<String> house = new ArrayList<String>();
	   
	   query = "SELECT * FROM House";
	
	   statment = connecton.prepareStatement(query);
	   result = statment.executeQuery();

		while (result.next()) 
		{
			house.add(result.getString(1));
			house.add(result.getString(2));
			house.add(result.getString(3));
			house.add(result.getString(4));
			house.add(result.getString(5));
			house.add(result.getString(6));
		}
		
		String allHouses = "";
		
		for (String s : house)
		{
			allHouses += s + "\t";
		}
		
		return allHouses;
	}
}//end of Connector